SUMPRODUCT

See Also

Multiplies corresponding components in the given arrays, and returns the sum of those products.

Syntax

SUMPRODUCT(array1,array2,array3, ...)

Array1, array2, array3, ... are 2 to 30 arrays whose components you want to multiply and then add.

Remarks

The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.
SUMPRODUCT treats array entries that are not numeric as if they were zeros.

Example

The example may be easier to understand if you copy it to a blank worksheet.

Show How?

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1

2
3
4

A	B	C	D
Array 1	Array 1	Array 2	Array 2
3	4	2	7
8	6	6	7
1	9	5	3
Formula	Description (Result)
=SUMPRODUCT(A2:B4, C2:D4)	Multiplies all the components of the two arrays and then adds the products— that is, 32 + 47 + 86 + 67 + 15 + 93. (156)

Remark

The preceding example returns the same result as the formula SUM(A2:B4*C2:D4) entered as an array. Using arrays provides a more general solution for doing operations similar to SUMPRODUCT. For example, you can calculate the sum of the squares of the elements in A2:B4 by using the formula =SUM(A2:B4^2) and pressing CTRL+SHIFT+ENTER.